{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第四阶段 - 第1讲：分析思维与指标设计\n",
    "\n",
    "## 学习目标\n",
    "- 建立数据驱动的分析思维\n",
    "- 掌握问题定义和分析框架\n",
    "- 理解四种核心分析思路（对比、结构、趋势、相关）\n",
    "- 学会设计业务指标体系\n",
    "- 掌握核心业务指标的计算方法\n",
    "- 理解SMART原则和北极星指标\n",
    "\n",
    "**重要性**: ⭐⭐⭐⭐⭐ 分析思维是数据分析的灵魂！\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from datetime import datetime, timedelta\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 设置\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.float_format', '{:.2f}'.format)\n",
    "plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "print(\"✅ 环境配置完成\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、数据分析思维框架\n",
    "\n",
    "### 1.1 问题驱动 vs 数据驱动\n",
    "\n",
    "#### 问题驱动（推荐）\n",
    "```\n",
    "业务问题 → 数据需求 → 指标选择 → 分析方法 → 得出结论\n",
    "```\n",
    "**特点**: 目标明确，结论有用\n",
    "\n",
    "**示例**:\n",
    "- ❌ 不好: \"给我分析一下这个月的销售数据\"\n",
    "- ✅ 好: \"为什么这个月销售额下降了15%？哪些产品/地区下降最严重？\"\n",
    "\n",
    "#### 数据驱动（探索性）\n",
    "```\n",
    "数据探索 → 发现模式 → 提出假设 → 验证假设 → 业务建议\n",
    "```\n",
    "**特点**: 发现隐藏洞察\n",
    "\n",
    "### 1.2 从业务到数据的转化\n",
    "\n",
    "| 业务问题 | 数据问题 | 分析方法 |\n",
    "|---------|---------|----------|\n",
    "| 销售为何下降？ | 销售额环比/同比变化 | 对比分析 |\n",
    "| 哪个产品最畅销？ | 各产品销售额排名 | 结构分析 |\n",
    "| 销售趋势如何？ | 各时间点销售额变化 | 趋势分析 |\n",
    "| 广告是否有效？ | 广告投入与销售额关系 | 相关分析 |\n",
    "| 如何提升利润？ | 利润影响因素分析 | 多维分析 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 分析流程五步法\n",
    "\n",
    "#### 第一步：明确问题\n",
    "- 业务背景是什么？\n",
    "- 想回答什么问题？\n",
    "- 分析目的是什么？\n",
    "- 决策依据是什么？\n",
    "\n",
    "#### 第二步：数据准备\n",
    "- 需要哪些数据？\n",
    "- 数据从哪里来？\n",
    "- 数据质量如何？\n",
    "- 是否需要清洗？\n",
    "\n",
    "#### 第三步：指标选择\n",
    "- 哪些指标能回答问题？\n",
    "- 指标如何计算？\n",
    "- 指标的合理范围？\n",
    "- 对比基准是什么？\n",
    "\n",
    "#### 第四步：数据分析\n",
    "- 选择合适的分析方法\n",
    "- 计算指标\n",
    "- 发现数据规律\n",
    "- 验证假设\n",
    "\n",
    "#### 第五步：得出结论\n",
    "- 发现了什么？（现象）\n",
    "- 为什么这样？（原因）\n",
    "- 应该怎么做？（建议）\n",
    "- 预期效果如何？（影响）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建示例数据 - 电商销售数据\n",
    "np.random.seed(42)\n",
    "n_months = 12\n",
    "n_products = 5\n",
    "\n",
    "# 生成月度数据\n",
    "dates = pd.date_range('2023-01-01', periods=n_months, freq='MS')\n",
    "products = ['手机', '电脑', '平板', '耳机', '手表']\n",
    "regions = ['华东', '华北', '华南', '华中', '西南']\n",
    "\n",
    "# 创建数据\n",
    "data_list = []\n",
    "for date in dates:\n",
    "    for product in products:\n",
    "        for region in regions:\n",
    "            # 添加季节性和趋势\n",
    "            month = date.month\n",
    "            base_sales = np.random.randint(50, 150)\n",
    "            \n",
    "            # 季节性：11-12月增加（双11、双12、年底）\n",
    "            if month in [11, 12]:\n",
    "                base_sales *= 1.5\n",
    "            # 6-8月促销\n",
    "            elif month in [6, 7, 8]:\n",
    "                base_sales *= 1.2\n",
    "            \n",
    "            data_list.append({\n",
    "                'date': date,\n",
    "                'year': date.year,\n",
    "                'month': date.month,\n",
    "                'quarter': date.quarter,\n",
    "                'product': product,\n",
    "                'region': region,\n",
    "                'sales_volume': int(base_sales),\n",
    "                'unit_price': np.random.choice([999, 1999, 2999, 4999, 6999]),\n",
    "                'cost_rate': np.random.uniform(0.5, 0.7)\n",
    "            })\n",
    "\n",
    "df = pd.DataFrame(data_list)\n",
    "\n",
    "# 计算衍生指标\n",
    "df['sales_amount'] = df['sales_volume'] * df['unit_price']\n",
    "df['cost'] = df['sales_amount'] * df['cost_rate']\n",
    "df['profit'] = df['sales_amount'] - df['cost']\n",
    "df['profit_margin'] = df['profit'] / df['sales_amount']\n",
    "\n",
    "print(\"✅ 创建了电商销售数据\")\n",
    "print(f\"数据规模: {df.shape}\")\n",
    "print(f\"时间跨度: {df['date'].min().date()} 至 {df['date'].max().date()}\")\n",
    "print(f\"产品数: {df['product'].nunique()}\")\n",
    "print(f\"地区数: {df['region'].nunique()}\")\n",
    "print(\"\\n数据预览:\")\n",
    "print(df.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 二、四种核心分析思路\n",
    "\n",
    "### 2.1 对比分析\n",
    "\n",
    "**目的**: 发现差异，找到问题或机会\n",
    "\n",
    "**常见对比维度**:\n",
    "- **时间对比**: 同比、环比、定基比\n",
    "- **空间对比**: 地区对比、渠道对比\n",
    "- **分类对比**: 产品对比、客户群对比\n",
    "- **目标对比**: 实际vs目标、实际vs预算\n",
    "\n",
    "#### 同比、环比、定基比\n",
    "\n",
    "- **同比**: 与去年同期相比 `(今年1月 - 去年1月) / 去年1月`\n",
    "- **环比**: 与上一期相比 `(本月 - 上月) / 上月`\n",
    "- **定基比**: 与基期相比 `(本期 - 基期) / 基期`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对比分析示例\n",
    "print(\"=== 对比分析 ===\")\n",
    "\n",
    "# 1. 产品对比\n",
    "product_comparison = df.groupby('product').agg({\n",
    "    'sales_amount': 'sum',\n",
    "    'sales_volume': 'sum',\n",
    "    'profit': 'sum'\n",
    "}).round(0)\n",
    "\n",
    "product_comparison['平均单价'] = (product_comparison['sales_amount'] / product_comparison['sales_volume']).round(0)\n",
    "product_comparison['利润率(%)'] = (product_comparison['profit'] / product_comparison['sales_amount'] * 100).round(2)\n",
    "product_comparison.columns = ['销售额', '销量', '利润', '平均单价', '利润率(%)']\n",
    "product_comparison = product_comparison.sort_values('销售额', ascending=False)\n",
    "\n",
    "print(\"\\n产品对比分析:\")\n",
    "print(product_comparison)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. 地区对比\n",
    "region_comparison = df.groupby('region').agg({\n",
    "    'sales_amount': 'sum',\n",
    "    'profit': 'sum'\n",
    "}).round(0)\n",
    "\n",
    "region_comparison['利润率(%)'] = (region_comparison['profit'] / region_comparison['sales_amount'] * 100).round(2)\n",
    "region_comparison.columns = ['销售额', '利润', '利润率(%)']\n",
    "region_comparison = region_comparison.sort_values('销售额', ascending=False)\n",
    "\n",
    "print(\"\\n地区对比分析:\")\n",
    "print(region_comparison)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. 环比分析\n",
    "monthly_sales = df.groupby('month')['sales_amount'].sum().reset_index()\n",
    "monthly_sales['环比增长额'] = monthly_sales['sales_amount'].diff()\n",
    "monthly_sales['环比增长率(%)'] = (monthly_sales['sales_amount'].pct_change() * 100).round(2)\n",
    "monthly_sales.columns = ['月份', '销售额', '环比增长额', '环比增长率(%)']\n",
    "\n",
    "print(\"\\n月度环比分析:\")\n",
    "print(monthly_sales)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化对比分析\n",
    "fig, axes = plt.subplots(2, 2, figsize=(16, 10))\n",
    "\n",
    "# 1. 产品销售额对比\n",
    "product_comparison['销售额'].plot(kind='barh', ax=axes[0, 0], color='steelblue', edgecolor='black')\n",
    "axes[0, 0].set_title('Product Sales Comparison', fontsize=14, fontweight='bold')\n",
    "axes[0, 0].set_xlabel('Sales Amount')\n",
    "axes[0, 0].grid(axis='x', alpha=0.3)\n",
    "\n",
    "# 2. 地区销售额对比\n",
    "region_comparison['销售额'].plot(kind='bar', ax=axes[0, 1], color='coral', edgecolor='black')\n",
    "axes[0, 1].set_title('Regional Sales Comparison', fontsize=14, fontweight='bold')\n",
    "axes[0, 1].set_ylabel('Sales Amount')\n",
    "axes[0, 1].tick_params(axis='x', rotation=45)\n",
    "axes[0, 1].grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 3. 产品利润率对比\n",
    "product_comparison['利润率(%)'].plot(kind='barh', ax=axes[1, 0], color='lightgreen', edgecolor='black')\n",
    "axes[1, 0].set_title('Product Profit Margin Comparison', fontsize=14, fontweight='bold')\n",
    "axes[1, 0].set_xlabel('Profit Margin (%)')\n",
    "axes[1, 0].grid(axis='x', alpha=0.3)\n",
    "\n",
    "# 4. 月度环比增长率\n",
    "axes[1, 1].plot(monthly_sales['月份'], monthly_sales['环比增长率(%)'], marker='o', linewidth=2, markersize=8, color='purple')\n",
    "axes[1, 1].axhline(y=0, color='red', linestyle='--', alpha=0.5)\n",
    "axes[1, 1].set_title('Month-over-Month Growth Rate', fontsize=14, fontweight='bold')\n",
    "axes[1, 1].set_xlabel('Month')\n",
    "axes[1, 1].set_ylabel('MoM Growth Rate (%)')\n",
    "axes[1, 1].grid(alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 结构分析\n",
    "\n",
    "**目的**: 了解组成和占比，找到重点\n",
    "\n",
    "**常见分析**:\n",
    "- **占比分析**: 各部分占整体的比例\n",
    "- **帕累托分析**: 80/20法则，找出关键少数\n",
    "- **贡献度分析**: 各部分对增长的贡献\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 手动计算占比，绘制饼图\n",
    "- Pandas: 自动计算，cumsum()求累计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 结构分析示例\n",
    "print(\"=== 结构分析 ===\")\n",
    "\n",
    "# 1. 产品销售额占比\n",
    "product_structure = df.groupby('product')['sales_amount'].sum().sort_values(ascending=False)\n",
    "product_structure_df = pd.DataFrame({\n",
    "    '销售额': product_structure,\n",
    "    '占比(%)': (product_structure / product_structure.sum() * 100).round(2),\n",
    "    '累计占比(%)': (product_structure / product_structure.sum() * 100).cumsum().round(2)\n",
    "})\n",
    "\n",
    "print(\"\\n产品销售额结构:\")\n",
    "print(product_structure_df)\n",
    "\n",
    "# 帕累托分析\n",
    "top_products = product_structure_df[product_structure_df['累计占比(%)'] <= 80]\n",
    "print(f\"\\n帕累托分析: 前{len(top_products)}个产品贡献了{top_products['占比(%)'].sum():.1f}%的销售额\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. 地区销售额占比\n",
    "region_structure = df.groupby('region')['sales_amount'].sum().sort_values(ascending=False)\n",
    "region_structure_df = pd.DataFrame({\n",
    "    '销售额': region_structure,\n",
    "    '占比(%)': (region_structure / region_structure.sum() * 100).round(2)\n",
    "})\n",
    "\n",
    "print(\"\\n地区销售额结构:\")\n",
    "print(region_structure_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化结构分析\n",
    "fig, axes = plt.subplots(2, 2, figsize=(16, 10))\n",
    "\n",
    "# 1. 产品占比饼图\n",
    "colors = ['#ff9999', '#66b3ff', '#99ff99', '#ffcc99', '#ff99cc']\n",
    "axes[0, 0].pie(product_structure, labels=product_structure.index, autopct='%1.1f%%',\n",
    "               colors=colors, startangle=90)\n",
    "axes[0, 0].set_title('Product Sales Structure', fontsize=14, fontweight='bold')\n",
    "\n",
    "# 2. 帕累托图\n",
    "ax_pareto = axes[0, 1]\n",
    "ax_pareto.bar(range(len(product_structure)), product_structure.values, color='steelblue', alpha=0.7)\n",
    "ax_pareto.set_xticks(range(len(product_structure)))\n",
    "ax_pareto.set_xticklabels(product_structure.index, rotation=45, ha='right')\n",
    "ax_pareto.set_ylabel('Sales Amount', color='steelblue')\n",
    "ax_pareto.tick_params(axis='y', labelcolor='steelblue')\n",
    "\n",
    "ax_pareto2 = ax_pareto.twinx()\n",
    "cumsum = (product_structure / product_structure.sum() * 100).cumsum()\n",
    "ax_pareto2.plot(range(len(cumsum)), cumsum.values, color='red', marker='o', linewidth=2, markersize=8)\n",
    "ax_pareto2.axhline(y=80, color='red', linestyle='--', alpha=0.5, label='80% Line')\n",
    "ax_pareto2.set_ylabel('Cumulative Percentage (%)', color='red')\n",
    "ax_pareto2.tick_params(axis='y', labelcolor='red')\n",
    "ax_pareto2.set_ylim([0, 105])\n",
    "ax_pareto2.legend(loc='lower right')\n",
    "axes[0, 1].set_title('Pareto Chart', fontsize=14, fontweight='bold')\n",
    "\n",
    "# 3. 地区占比环形图\n",
    "axes[1, 0].pie(region_structure, labels=region_structure.index, autopct='%1.1f%%',\n",
    "               colors=colors, startangle=90, wedgeprops={'edgecolor': 'white', 'linewidth': 2})\n",
    "centre_circle = plt.Circle((0, 0), 0.70, fc='white')\n",
    "axes[1, 0].add_artist(centre_circle)\n",
    "axes[1, 0].set_title('Regional Sales Structure', fontsize=14, fontweight='bold')\n",
    "\n",
    "# 4. 产品×地区结构热力图\n",
    "pivot_structure = df.pivot_table(values='sales_amount', index='product', columns='region', aggfunc='sum')\n",
    "sns.heatmap(pivot_structure, annot=True, fmt='.0f', cmap='YlOrRd', ax=axes[1, 1])\n",
    "axes[1, 1].set_title('Product × Region Sales Heatmap', fontsize=14, fontweight='bold')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 趋势分析\n",
    "\n",
    "**目的**: 观察变化规律，预测未来\n",
    "\n",
    "**常见分析**:\n",
    "- **时间趋势**: 按日/周/月/季/年观察\n",
    "- **增长趋势**: 持续增长/下降/波动\n",
    "- **季节性**: 周期性波动\n",
    "- **拐点识别**: 转折点在哪里\n",
    "\n",
    "**关键指标**:\n",
    "- 环比增长率\n",
    "- 同比增长率\n",
    "- 移动平均\n",
    "- 增长速度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 趋势分析示例\n",
    "print(\"=== 趋势分析 ===\")\n",
    "\n",
    "# 1. 月度销售趋势\n",
    "monthly_trend = df.groupby('month').agg({\n",
    "    'sales_amount': 'sum',\n",
    "    'sales_volume': 'sum',\n",
    "    'profit': 'sum'\n",
    "}).reset_index()\n",
    "\n",
    "monthly_trend['环比增长率(%)'] = (monthly_trend['sales_amount'].pct_change() * 100).round(2)\n",
    "monthly_trend.columns = ['月份', '销售额', '销量', '利润', '环比增长率(%)']\n",
    "\n",
    "print(\"\\n月度销售趋势:\")\n",
    "print(monthly_trend)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. 产品销售趋势\n",
    "product_trend = df.groupby(['month', 'product'])['sales_amount'].sum().reset_index()\n",
    "product_trend_pivot = product_trend.pivot(index='month', columns='product', values='sales_amount')\n",
    "\n",
    "print(\"\\n各产品月度销售趋势:\")\n",
    "print(product_trend_pivot.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. 计算移动平均（平滑趋势）\n",
    "monthly_trend['3月移动平均'] = monthly_trend['销售额'].rolling(window=3).mean().round(0)\n",
    "\n",
    "print(\"\\n带移动平均的月度趋势:\")\n",
    "print(monthly_trend[['月份', '销售额', '3月移动平均', '环比增长率(%)']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化趋势分析\n",
    "fig, axes = plt.subplots(2, 2, figsize=(16, 10))\n",
    "\n",
    "# 1. 月度销售额趋势（含移动平均）\n",
    "axes[0, 0].plot(monthly_trend['月份'], monthly_trend['销售额'], marker='o', linewidth=2, \n",
    "                markersize=8, label='实际销售额', color='steelblue')\n",
    "axes[0, 0].plot(monthly_trend['月份'], monthly_trend['3月移动平均'], linewidth=2, \n",
    "                label='3月移动平均', color='red', linestyle='--')\n",
    "axes[0, 0].set_title('Monthly Sales Trend with Moving Average', fontsize=14, fontweight='bold')\n",
    "axes[0, 0].set_xlabel('Month')\n",
    "axes[0, 0].set_ylabel('Sales Amount')\n",
    "axes[0, 0].legend()\n",
    "axes[0, 0].grid(alpha=0.3)\n",
    "\n",
    "# 2. 环比增长率趋势\n",
    "axes[0, 1].bar(monthly_trend['月份'], monthly_trend['环比增长率(%)'], \n",
    "               color=['green' if x > 0 else 'red' for x in monthly_trend['环比增长率(%)']],\n",
    "               edgecolor='black')\n",
    "axes[0, 1].axhline(y=0, color='black', linestyle='-', linewidth=0.8)\n",
    "axes[0, 1].set_title('Month-over-Month Growth Rate', fontsize=14, fontweight='bold')\n",
    "axes[0, 1].set_xlabel('Month')\n",
    "axes[0, 1].set_ylabel('MoM Growth Rate (%)')\n",
    "axes[0, 1].grid(axis='y', alpha=0.3)\n",
    "\n",
    "# 3. 各产品销售趋势\n",
    "for product in product_trend_pivot.columns:\n",
    "    axes[1, 0].plot(product_trend_pivot.index, product_trend_pivot[product], \n",
    "                    marker='o', linewidth=2, label=product)\n",
    "axes[1, 0].set_title('Product Sales Trend', fontsize=14, fontweight='bold')\n",
    "axes[1, 0].set_xlabel('Month')\n",
    "axes[1, 0].set_ylabel('Sales Amount')\n",
    "axes[1, 0].legend()\n",
    "axes[1, 0].grid(alpha=0.3)\n",
    "\n",
    "# 4. 面积图展示结构变化\n",
    "axes[1, 1].stackplot(product_trend_pivot.index, \n",
    "                     *[product_trend_pivot[col] for col in product_trend_pivot.columns],\n",
    "                     labels=product_trend_pivot.columns, alpha=0.7)\n",
    "axes[1, 1].set_title('Product Sales Structure Evolution', fontsize=14, fontweight='bold')\n",
    "axes[1, 1].set_xlabel('Month')\n",
    "axes[1, 1].set_ylabel('Sales Amount')\n",
    "axes[1, 1].legend(loc='upper left')\n",
    "axes[1, 1].grid(alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4 相关分析\n",
    "\n",
    "**目的**: 发现变量之间的关系\n",
    "\n",
    "**常见分析**:\n",
    "- **正相关**: A增加，B也增加（如广告投入↑，销售额↑）\n",
    "- **负相关**: A增加，B减少（如价格↑，销量↓）\n",
    "- **无相关**: A和B没有明显关系\n",
    "\n",
    "**相关系数**:\n",
    "- -1 到 1 之间\n",
    "- |r| > 0.7: 强相关\n",
    "- 0.3 < |r| < 0.7: 中等相关\n",
    "- |r| < 0.3: 弱相关\n",
    "\n",
    "**注意**: 相关不等于因果！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 相关分析示例\n",
    "print(\"=== 相关分析 ===\")\n",
    "\n",
    "# 创建月度汇总数据（包含多个指标）\n",
    "monthly_metrics = df.groupby('month').agg({\n",
    "    'sales_amount': 'sum',\n",
    "    'sales_volume': 'sum',\n",
    "    'profit': 'sum',\n",
    "    'unit_price': 'mean'\n",
    "}).reset_index()\n",
    "\n",
    "# 添加营销费用（模拟）\n",
    "monthly_metrics['marketing_cost'] = monthly_metrics['sales_amount'] * 0.1 + np.random.normal(0, 50000, len(monthly_metrics))\n",
    "\n",
    "print(\"\\n月度综合指标:\")\n",
    "print(monthly_metrics.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算相关系数矩阵\n",
    "correlation_matrix = monthly_metrics[['sales_amount', 'sales_volume', 'profit', 'unit_price', 'marketing_cost']].corr()\n",
    "\n",
    "print(\"\\n相关系数矩阵:\")\n",
    "print(correlation_matrix.round(3))\n",
    "\n",
    "print(\"\\n关键发现:\")\n",
    "print(f\"销售额与营销费用相关系数: {correlation_matrix.loc['sales_amount', 'marketing_cost']:.3f}\")\n",
    "print(f\"销售额与销量相关系数: {correlation_matrix.loc['sales_amount', 'sales_volume']:.3f}\")\n",
    "print(f\"销售额与利润相关系数: {correlation_matrix.loc['sales_amount', 'profit']:.3f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化相关分析\n",
    "fig, axes = plt.subplots(2, 2, figsize=(16, 10))\n",
    "\n",
    "# 1. 相关系数热力图\n",
    "sns.heatmap(correlation_matrix, annot=True, cmap='coolwarm', center=0, \n",
    "            fmt='.2f', square=True, ax=axes[0, 0])\n",
    "axes[0, 0].set_title('Correlation Heatmap', fontsize=14, fontweight='bold')\n",
    "\n",
    "# 2. 销售额 vs 营销费用散点图\n",
    "axes[0, 1].scatter(monthly_metrics['marketing_cost'], monthly_metrics['sales_amount'], \n",
    "                   s=100, alpha=0.6, color='steelblue')\n",
    "# 添加趋势线\n",
    "z = np.polyfit(monthly_metrics['marketing_cost'], monthly_metrics['sales_amount'], 1)\n",
    "p = np.poly1d(z)\n",
    "axes[0, 1].plot(monthly_metrics['marketing_cost'], p(monthly_metrics['marketing_cost']), \n",
    "                \"r--\", linewidth=2, label=f'y={z[0]:.2f}x+{z[1]:.0f}')\n",
    "axes[0, 1].set_title('Sales vs Marketing Cost', fontsize=14, fontweight='bold')\n",
    "axes[0, 1].set_xlabel('Marketing Cost')\n",
    "axes[0, 1].set_ylabel('Sales Amount')\n",
    "axes[0, 1].legend()\n",
    "axes[0, 1].grid(alpha=0.3)\n",
    "\n",
    "# 3. 销售额 vs 销量散点图\n",
    "axes[1, 0].scatter(monthly_metrics['sales_volume'], monthly_metrics['sales_amount'], \n",
    "                   s=100, alpha=0.6, color='coral')\n",
    "z2 = np.polyfit(monthly_metrics['sales_volume'], monthly_metrics['sales_amount'], 1)\n",
    "p2 = np.poly1d(z2)\n",
    "axes[1, 0].plot(monthly_metrics['sales_volume'], p2(monthly_metrics['sales_volume']), \n",
    "                \"r--\", linewidth=2)\n",
    "axes[1, 0].set_title('Sales Amount vs Volume', fontsize=14, fontweight='bold')\n",
    "axes[1, 0].set_xlabel('Sales Volume')\n",
    "axes[1, 0].set_ylabel('Sales Amount')\n",
    "axes[1, 0].grid(alpha=0.3)\n",
    "\n",
    "# 4. 配对图（选取主要指标）\n",
    "from pandas.plotting import scatter_matrix\n",
    "scatter_data = monthly_metrics[['sales_amount', 'profit', 'marketing_cost']]\n",
    "pd.plotting.scatter_matrix(scatter_data, alpha=0.6, figsize=(6, 6), \n",
    "                          diagonal='kde', ax=axes[1, 1])\n",
    "axes[1, 1].set_title('Scatter Matrix', fontsize=14, fontweight='bold')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 三、核心业务指标体系\n",
    "\n",
    "### 3.1 指标分类\n",
    "\n",
    "#### 1. 规模指标\n",
    "- 销售额、订单量、用户数、访问量\n",
    "- **作用**: 反映业务规模\n",
    "\n",
    "#### 2. 效率指标\n",
    "- 客单价、转化率、人效、坪效\n",
    "- **作用**: 反映运营效率\n",
    "\n",
    "#### 3. 质量指标\n",
    "- 满意度、留存率、复购率、NPS\n",
    "- **作用**: 反映服务质量\n",
    "\n",
    "#### 4. 增长指标\n",
    "- 同比增长率、环比增长率、CAGR\n",
    "- **作用**: 反映增长态势\n",
    "\n",
    "#### 5. 财务指标\n",
    "- 利润、利润率、ROI、毛利率\n",
    "- **作用**: 反映盈利能力\n",
    "\n",
    "### 3.2 常用业务指标计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"=== 核心业务指标计算 ===\")\n",
    "\n",
    "# 汇总数据\n",
    "total_sales = df['sales_amount'].sum()\n",
    "total_volume = df['sales_volume'].sum()\n",
    "total_profit = df['profit'].sum()\n",
    "total_cost = df['cost'].sum()\n",
    "order_count = len(df)  # 简化：每行当作一个订单\n",
    "\n",
    "print(\"\\n【规模指标】\")\n",
    "print(f\"总销售额: ¥{total_sales:,.0f}\")\n",
    "print(f\"总销量: {total_volume:,}件\")\n",
    "print(f\"订单数: {order_count:,}单\")\n",
    "\n",
    "print(\"\\n【效率指标】\")\n",
    "avg_order_value = total_sales / order_count\n",
    "print(f\"客单价: ¥{avg_order_value:,.2f}\")\n",
    "avg_unit_price = total_sales / total_volume\n",
    "print(f\"平均单价: ¥{avg_unit_price:,.2f}\")\n",
    "\n",
    "print(\"\\n【财务指标】\")\n",
    "print(f\"总利润: ¥{total_profit:,.0f}\")\n",
    "profit_margin = total_profit / total_sales * 100\n",
    "print(f\"利润率: {profit_margin:.2f}%\")\n",
    "gross_margin = (total_sales - total_cost) / total_sales * 100\n",
    "print(f\"毛利率: {gross_margin:.2f}%\")\n",
    "\n",
    "print(\"\\n【增长指标】\")\n",
    "# 简化：对比前6个月和后6个月\n",
    "first_half = df[df['month'] <= 6]['sales_amount'].sum()\n",
    "second_half = df[df['month'] > 6]['sales_amount'].sum()\n",
    "growth_rate = (second_half - first_half) / first_half * 100\n",
    "print(f\"下半年vs上半年增长率: {growth_rate:.2f}%\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按产品计算指标\n",
    "product_metrics = df.groupby('product').agg({\n",
    "    'sales_amount': 'sum',\n",
    "    'sales_volume': 'sum',\n",
    "    'profit': 'sum',\n",
    "    'cost': 'sum'\n",
    "})\n",
    "\n",
    "product_metrics['客单价'] = product_metrics['sales_amount'] / df.groupby('product').size()\n",
    "product_metrics['平均单价'] = product_metrics['sales_amount'] / product_metrics['sales_volume']\n",
    "product_metrics['利润率(%)'] = product_metrics['profit'] / product_metrics['sales_amount'] * 100\n",
    "product_metrics['毛利率(%)'] = (product_metrics['sales_amount'] - product_metrics['cost']) / product_metrics['sales_amount'] * 100\n",
    "\n",
    "product_metrics = product_metrics[['客单价', '平均单价', '利润率(%)', '毛利率(%)']].round(2)\n",
    "product_metrics = product_metrics.sort_values('利润率(%)', ascending=False)\n",
    "\n",
    "print(\"\\n=== 各产品核心指标 ===\")\n",
    "print(product_metrics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3 电商核心指标\n",
    "\n",
    "#### GMV (Gross Merchandise Volume)\n",
    "**成交总额** = 订单数 × 客单价\n",
    "\n",
    "#### 客单价\n",
    "**客单价** = 销售额 / 订单数\n",
    "\n",
    "#### 转化率\n",
    "**转化率** = 下单用户数 / 访问用户数 × 100%\n",
    "\n",
    "#### 复购率\n",
    "**复购率** = 购买2次及以上的用户数 / 总用户数 × 100%\n",
    "\n",
    "#### 用户留存率\n",
    "**次日留存** = 第2天还活跃的用户数 / 第1天新增用户数 × 100%"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4 SMART原则\n",
    "\n",
    "好的指标应该符合SMART原则：\n",
    "\n",
    "- **S (Specific)**: 明确具体\n",
    "  - ❌ \"提升销售额\"\n",
    "  - ✅ \"Q2销售额达到1000万\"\n",
    "\n",
    "- **M (Measurable)**: 可衡量\n",
    "  - ❌ \"提高用户满意度\"\n",
    "  - ✅ \"NPS从60提升到70\"\n",
    "\n",
    "- **A (Achievable)**: 可实现\n",
    "  - ❌ \"销售额增长1000%\"\n",
    "  - ✅ \"销售额增长20%\"\n",
    "\n",
    "- **R (Relevant)**: 相关联\n",
    "  - 指标要与业务目标相关\n",
    "\n",
    "- **T (Time-bound)**: 有时限\n",
    "  - ❌ \"销售额达到1000万\"\n",
    "  - ✅ \"6月底前销售额达到1000万\"\n",
    "\n",
    "### 3.5 北极星指标\n",
    "\n",
    "**定义**: 最能反映产品核心价值的关键指标\n",
    "\n",
    "**示例**:\n",
    "- **淘宝**: GMV（成交总额）\n",
    "- **抖音**: 日活跃用户数(DAU) × 使用时长\n",
    "- **Airbnb**: 预定间夜数\n",
    "- **美团**: 每月交易用户数\n",
    "- **Netflix**: 有效播放时长\n",
    "\n",
    "**特点**:\n",
    "1. 反映用户价值\n",
    "2. 可分解可执行\n",
    "3. 与收入相关\n",
    "4. 全公司聚焦"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 四、实战案例：搭建电商指标体系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 构建完整的指标体系\n",
    "print(\"=\"*70)\n",
    "print(\"电商业务指标体系\")\n",
    "print(\"=\"*70)\n",
    "\n",
    "# 一级指标：整体业务\n",
    "print(\"\\n【一级指标：整体业务】\")\n",
    "print(f\"GMV（成交总额）: ¥{total_sales:,.0f}\")\n",
    "print(f\"订单量: {order_count:,}\")\n",
    "print(f\"客单价: ¥{avg_order_value:,.2f}\")\n",
    "print(f\"毛利率: {gross_margin:.2f}%\")\n",
    "\n",
    "# 二级指标：流量指标（模拟）\n",
    "print(\"\\n【二级指标：流量指标】（模拟）\")\n",
    "visitors = order_count * 10  # 假设转化率10%\n",
    "conversion_rate = order_count / visitors * 100\n",
    "print(f\"访客数: {visitors:,}\")\n",
    "print(f\"转化率: {conversion_rate:.2f}%\")\n",
    "\n",
    "# 三级指标：产品指标\n",
    "print(\"\\n【三级指标：产品指标】\")\n",
    "top_products = df.groupby('product')['sales_amount'].sum().sort_values(ascending=False).head(3)\n",
    "print(\"TOP3产品:\")\n",
    "for i, (product, sales) in enumerate(top_products.items(), 1):\n",
    "    pct = sales / total_sales * 100\n",
    "    print(f\"  {i}. {product}: ¥{sales:,.0f} ({pct:.1f}%)\")\n",
    "\n",
    "# 四级指标：地区指标\n",
    "print(\"\\n【四级指标：地区指标】\")\n",
    "top_regions = df.groupby('region')['sales_amount'].sum().sort_values(ascending=False).head(3)\n",
    "print(\"TOP3地区:\")\n",
    "for i, (region, sales) in enumerate(top_regions.items(), 1):\n",
    "    pct = sales / total_sales * 100\n",
    "    print(f\"  {i}. {region}: ¥{sales:,.0f} ({pct:.1f}%)\")\n",
    "\n",
    "# 增长指标\n",
    "print(\"\\n【增长指标】\")\n",
    "print(f\"环比增长率: {growth_rate:.2f}%\")\n",
    "month_max = df.groupby('month')['sales_amount'].sum().idxmax()\n",
    "month_max_sales = df.groupby('month')['sales_amount'].sum().max()\n",
    "print(f\"销售峰值月份: {month_max}月 (¥{month_max_sales:,.0f})\")\n",
    "\n",
    "print(\"\\n\" + \"=\"*70)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化指标体系\n",
    "fig = plt.figure(figsize=(16, 10))\n",
    "gs = fig.add_gridspec(3, 3, hspace=0.3, wspace=0.3)\n",
    "\n",
    "# 1. 核心指标卡片式展示\n",
    "metrics_cards = [\n",
    "    ('GMV', f'¥{total_sales/1e6:.1f}M'),\n",
    "    ('订单量', f'{order_count:,}'),\n",
    "    ('客单价', f'¥{avg_order_value:.0f}'),\n",
    "    ('利润率', f'{profit_margin:.1f}%'),\n",
    "    ('环比增长', f'{growth_rate:.1f}%')\n",
    "]\n",
    "\n",
    "for i, (label, value) in enumerate(metrics_cards):\n",
    "    ax = fig.add_subplot(gs[0, i % 3])\n",
    "    ax.text(0.5, 0.6, value, ha='center', va='center', fontsize=24, fontweight='bold', color='steelblue')\n",
    "    ax.text(0.5, 0.3, label, ha='center', va='center', fontsize=14, color='gray')\n",
    "    ax.set_xlim(0, 1)\n",
    "    ax.set_ylim(0, 1)\n",
    "    ax.axis('off')\n",
    "    # 添加边框\n",
    "    rect = plt.Rectangle((0.05, 0.1), 0.9, 0.8, fill=False, edgecolor='lightgray', linewidth=2)\n",
    "    ax.add_patch(rect)\n",
    "\n",
    "# 2. 月度趋势\n",
    "ax2 = fig.add_subplot(gs[1, :])\n",
    "monthly_sales_plot = df.groupby('month')['sales_amount'].sum()\n",
    "ax2.plot(monthly_sales_plot.index, monthly_sales_plot.values, marker='o', linewidth=2, markersize=8, color='steelblue')\n",
    "ax2.fill_between(monthly_sales_plot.index, monthly_sales_plot.values, alpha=0.3)\n",
    "ax2.set_title('Monthly Sales Trend', fontsize=14, fontweight='bold')\n",
    "ax2.set_xlabel('Month')\n",
    "ax2.set_ylabel('Sales Amount')\n",
    "ax2.grid(alpha=0.3)\n",
    "\n",
    "# 3. 产品占比\n",
    "ax3 = fig.add_subplot(gs[2, 0])\n",
    "product_sales = df.groupby('product')['sales_amount'].sum().sort_values(ascending=False)\n",
    "ax3.pie(product_sales, labels=product_sales.index, autopct='%1.1f%%', startangle=90)\n",
    "ax3.set_title('Product Sales Structure', fontsize=12, fontweight='bold')\n",
    "\n",
    "# 4. 地区对比\n",
    "ax4 = fig.add_subplot(gs[2, 1])\n",
    "region_sales = df.groupby('region')['sales_amount'].sum().sort_values(ascending=False)\n",
    "ax4.barh(range(len(region_sales)), region_sales.values, color='coral', edgecolor='black')\n",
    "ax4.set_yticks(range(len(region_sales)))\n",
    "ax4.set_yticklabels(region_sales.index)\n",
    "ax4.set_title('Regional Sales Ranking', fontsize=12, fontweight='bold')\n",
    "ax4.set_xlabel('Sales Amount')\n",
    "ax4.grid(axis='x', alpha=0.3)\n",
    "\n",
    "# 5. 利润率对比\n",
    "ax5 = fig.add_subplot(gs[2, 2])\n",
    "product_profit_margin = (df.groupby('product')['profit'].sum() / df.groupby('product')['sales_amount'].sum() * 100).sort_values(ascending=False)\n",
    "ax5.bar(range(len(product_profit_margin)), product_profit_margin.values, color='lightgreen', edgecolor='black')\n",
    "ax5.set_xticks(range(len(product_profit_margin)))\n",
    "ax5.set_xticklabels(product_profit_margin.index, rotation=45, ha='right')\n",
    "ax5.set_title('Product Profit Margin', fontsize=12, fontweight='bold')\n",
    "ax5.set_ylabel('Profit Margin (%)')\n",
    "ax5.grid(axis='y', alpha=0.3)\n",
    "\n",
    "plt.suptitle('Business Metrics Dashboard', fontsize=16, fontweight='bold', y=0.98)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 五、本讲总结\n",
    "\n",
    "### 核心知识点\n",
    "\n",
    "#### 1. 分析思维框架\n",
    "- **问题驱动**: 明确目标 → 选择指标 → 分析 → 结论\n",
    "- **数据驱动**: 探索 → 发现 → 假设 → 验证\n",
    "- **五步法**: 问题→数据→指标→分析→结论\n",
    "\n",
    "#### 2. 四种分析思路\n",
    "\n",
    "| 思路 | 目的 | 方法 | 应用 |\n",
    "|------|------|------|------|\n",
    "| 对比 | 找差异 | 同比/环比/分类对比 | 业绩评估 |\n",
    "| 结构 | 看占比 | 帕累托/占比 | 资源分配 |\n",
    "| 趋势 | 观变化 | 时间序列/移动平均 | 预测规划 |\n",
    "| 相关 | 找关系 | 相关系数/散点图 | 因素分析 |\n",
    "\n",
    "#### 3. 核心指标体系\n",
    "\n",
    "**五大类指标**:\n",
    "1. 规模指标: 销售额、订单量\n",
    "2. 效率指标: 客单价、转化率\n",
    "3. 质量指标: 满意度、留存率\n",
    "4. 增长指标: 同比、环比\n",
    "5. 财务指标: 利润、利润率\n",
    "\n",
    "**常用计算**:\n",
    "- 客单价 = 销售额 / 订单数\n",
    "- 转化率 = 下单数 / 访客数\n",
    "- 利润率 = 利润 / 销售额\n",
    "- 同比增长 = (本期 - 去年同期) / 去年同期\n",
    "- 环比增长 = (本期 - 上期) / 上期\n",
    "\n",
    "#### 4. SMART原则\n",
    "- S: 具体的\n",
    "- M: 可衡量\n",
    "- A: 可实现\n",
    "- R: 相关的\n",
    "- T: 有时限\n",
    "\n",
    "#### 5. 北极星指标\n",
    "- 最能反映产品核心价值\n",
    "- 可分解、可执行\n",
    "- 全公司聚焦\n",
    "\n",
    "### Excel vs Pandas\n",
    "\n",
    "| 任务 | Excel | Pandas |\n",
    "|------|-------|--------|\n",
    "| 同比环比 | 手动公式 | `pct_change()` |\n",
    "| 移动平均 | 复杂公式 | `rolling().mean()` |\n",
    "| 占比计算 | 分别计算 | 向量化计算 |\n",
    "| 相关分析 | 数据分析工具 | `corr()` |\n",
    "| 多维分析 | 数据透视表 | `groupby()` + `pivot_table()` |\n",
    "\n",
    "### 关键要点\n",
    "\n",
    "1. **先想后做**: 明确问题再分析\n",
    "2. **指标要少**: 聚焦核心指标\n",
    "3. **要有对比**: 没有对比就没有发现\n",
    "4. **讲故事**: 数据→洞察→建议\n",
    "5. **业务视角**: 从业务角度解读数据\n",
    "\n",
    "### 下节预告\n",
    "**第2讲: 描述性统计分析**\n",
    "- 集中趋势度量（均值、中位数、众数）\n",
    "- 离散程度度量（方差、标准差、分位数）\n",
    "- 分布形态（偏度、峰度）\n",
    "- Pandas统计函数大全\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 课后作业\n",
    "\n",
    "### 作业1: 分析思路练习\n",
    "针对以下业务问题，设计分析思路:\n",
    "1. \"为什么这个月销售额下降了？\" - 用对比分析\n",
    "2. \"哪些产品是我们的主力产品？\" - 用结构分析\n",
    "3. \"未来3个月销售趋势如何？\" - 用趋势分析\n",
    "4. \"广告投入是否有效？\" - 用相关分析\n",
    "\n",
    "对每个问题:\n",
    "- 需要哪些数据？\n",
    "- 计算哪些指标？\n",
    "- 如何可视化？\n",
    "- 预期发现什么？\n",
    "\n",
    "### 作业2: 指标体系搭建\n",
    "为以下业务搭建指标体系:\n",
    "1. 在线教育平台\n",
    "2. 外卖平台\n",
    "3. 共享单车\n",
    "\n",
    "要求:\n",
    "- 明确北极星指标\n",
    "- 设计3层指标体系\n",
    "- 说明每个指标的计算方法\n",
    "- 指标之间的关系\n",
    "\n",
    "### 作业3: 实战分析\n",
    "使用提供的销售数据，完成:\n",
    "1. 对比分析：产品/地区/时间多维对比\n",
    "2. 结构分析：帕累托图，找出关键产品\n",
    "3. 趋势分析：月度趋势，预测下月销售额\n",
    "4. 相关分析：找出影响销售额的关键因素\n",
    "5. 综合报告：整合以上分析，给出业务建议\n",
    "\n",
    "### 作业4: 指标计算\n",
    "编写函数计算以下指标:\n",
    "1. 同比增长率\n",
    "2. 环比增长率\n",
    "3. 客单价\n",
    "4. 转化率\n",
    "5. 复购率\n",
    "6. 用户留存率\n",
    "7. RFM评分\n",
    "\n",
    "要求:\n",
    "- 函数可复用\n",
    "- 参数灵活\n",
    "- 有文档说明\n",
    "- 包含测试用例"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
